<?php

$dbhost = "localhost";
$dbuser = "root";
$dbpassword = "";

$database = "ThreadExtraction";
$dataTable = "Data";
$annotationsTable = "Annotations";
$fileTable = "Files";

if(isset($_GET['filenumber'])) {
$page = $_GET['page']; // get the requested page 
$limit = $_GET['rows']; // get how many rows we want to have into the grid 
$sidx = $_GET['sidx']; // get index row - i.e. user click to sort 
$sord = $_GET['sord']; // get the direction 
$filenumber = $_GET['filenumber'];
$annotator1 = $_GET['annotator1'];
$annotator2 = $_GET['annotator2'];
} else{
  $filenumber = $annotator1 = $annotator2 = 1;
}
if(!$sidx) {
	$sidx =1; 
	$sord = "asc";
}
// connect to the database 
$db = mysql_connect($dbhost, $dbuser, $dbpassword) or die("Connection Error: " . mysql_error()); 
mysql_select_db($database) or die("Error conecting to db."); 

$result = mysql_query("SELECT COUNT(*) AS count FROM Data where filenumber=$filenumber"); 
$row = mysql_fetch_array($result,MYSQL_ASSOC); 
$count = $row['count']; 

if( $count >0 && $limit > 0) { 
	$total_pages = ceil($count/$limit); 
} else { 
	$total_pages = 0; 
} 

if ($page > $total_pages) 
	$page=$total_pages; 

$start = $limit*$page - $limit; 

// do not put $limit*($page - 1) 

$dataSQL = "create temporary table postsToUse select * from data where filenumber=$filenumber;";

$data = mysql_query($dataSQL) or die("Couldn't execute data query.".mysql_error()); 

$dataSQL = "create temporary table annots select a.postid, a.parent as parenta, a.relation as relationa, a.thread as threada, b.parent, b.relation, b.thread from annotations a, annotations b where a.postid = b.postid and a.postid in (select id from postsToUse) and a.annotator = $annotator1 and b.annotator = $annotator2;";
$data = mysql_query($dataSQL) or die("Couldn't execute data query.".mysql_error()); 
$dataSQL = "select * from postsToUse d left join annots a on a.postid = d.id;";

$data = mysql_query($dataSQL) or die("Couldn't execute data query.".mysql_error()); 

$response->page = $page; $response->total = $total_pages; 
$response->userdata = array("fileno" => $filenumber, "filename" => $filename);
$response->records = $count; 
$i=0; 

$order = array("postnum", "timestamp", "system", "user", "content", "parenta", "relationa", "threada", "parent", "relation", "thread");

while($row = mysql_fetch_array($data, MYSQL_ASSOC)) { 
//        print_r($hash);
//        print_r($row);
        $hash[$row[postnum]] = $i;
	$response->rows[$i]['id']=$row[postnum];
	unset($row[id]);
	$response->rows[$i]['cell']= array_map(getVals, $order);
	$i++; 
} 
#print_r($response);
echo json_encode($response);

$dataSQL = "select * from annots where postnum in (select in from postsToUse)";

function getVals($v) {
  global $row;
  return $row[$v];
}
?>
